import xlrd

file_path = "baidu-员工的人员信息.xls"
workbook = xlrd.open_workbook(file_path)
sheet = workbook.sheet_by_index(0)
# 统计表格中有多少人
rows = sheet.nrows
print(rows)


def test1():
    # 统计办电信，联通，移动的用户数量并计算出三种用户的占比
    yd_str = '134.135.136.137.138.139.147.150.151.152.157.158.159.178.182.183.184.187.188.1703.1705.1706'
    lt_str = '130.131.132.145.155.156.175.176.185.186.1704.1707.1708.1709.171'
    dx_str = '133.149.153.173.177.180.181.189.1700.1701.1702'
    mobile_prefix = {
        '移动': yd_str.split('.'),
        '联通': lt_str.split('.'),
        '电信': dx_str.split('.')
    }
    counts = {'移动': 0, '联通': 0, '电信': 0}
    # 计算每个运营商的使用数量
    for i in range(1, rows):
        mobile_number = str(sheet.cell_value(i, 5))
        for k, v in mobile_prefix.items():
            if any(mobile_number.startswith(prefix) for prefix in v):
                counts[k] += 1
                break
    person_sum = 65534
    print(f"每个运营商的占比为：")
    print(f"移动：{counts['移动'] / person_sum * 100}%")
    print(f"移动：{counts['联通'] / person_sum * 100}%")
    print(f"移动：{counts['电信'] / person_sum * 100}%")


def test2():
    counts = {'男': 0, '女': 0}
    for i in range(1, rows):
        sex = str(sheet.cell_value(i, 8))
        counts[sex] += 1
    print(f"总公司男女数：男：{counts['男']}人，女：{counts['女']}人")


def test3():
    count = 0
    for i in range(1, rows):
        age = int(sheet.cell_value(i, 7))
        if age > 45:
            count += 1
    print(f"年龄超过45岁的老员工人数：{count}")


def test4():
    count = 0
    count1 = 0
    for i in range(1, rows):
        salary = float(sheet.cell_value(i, 11))
        if salary > 8000:
            count += 1
        if salary < 3000:
            count1 += 1
    print(f"薪资高于8000元的高薪人员数量：{count}")
    print(f"薪资低于3000的底薪人员数量：{count1}")


def test5():
    count = 0
    for i in range(1, rows):
        company = str(sheet.cell_value(i, 13))

        if company.find('传媒') != -1:
            count += 1
    print(f"去传媒公司的工作的人员数量：{count}")


def test6():
    count = 0
    addresses = ['黑龙江', '北京', '福建', '四川']
    for i in range(1, rows):
        address = str(sheet.cell_value(i, 9))
        if any(address.startswith(ar) for ar in addresses):
            count += 1
    print(f"在疫情高危地区的人数：{count}")


test6()
